hi, take one script task.you can check below link. http://codingsense.wordpress.com/2009/05/11/create-execute-and-delete-macro-in-excel-using-c/ i think it may help ful.indu

Why would you want to disable them? How can they interfere? In general it is an amateur design decision to process Excel sheets that are user created - they are too volatile or unpredictable and therefore packages consuming such Excel sheets break more often than work well. Excel should not in theory proliferate beyond user desktops.Arthur My Blog

Thanks for the reply. I also have a problem, on Second tab , second column, I have to change the datatype, How can I do it using Script Task. Basically I have to change the formatting type to general for the second column in second tab. Anky

We are getting source data in excel format, when we are trying to connect to the macros enabled excel sheet, it doesnot accept it, the source connection is taking the file extension as .xlsm, which is not valid for Excel file connection. It takes only .xlsx & .xlsAnky

If you cannot get whoever is providing the files to give it to you in the proper format then you'll need to convert the .xlsm files to .xlsx either using the excel object model to do a save as or one of the many free file convertors.Chuck

in VB Sub Workbook_Opn_DisableMAcros() Dim oldSecurity oldSecurity = Excel.Application.AutomationSecurity Excel.Application.AutomationSecurity = msoAutomationSecurityForceDisable Excel.Workbooks.Open ("C:\Macro.xls") Excel.Application.AutomationSecurity = oldSecurity End Sub Arthur My Blog

in VB Sub Workbook_Opn_DisableMAcros() Dim oldSecurity oldSecurity = Excel.Application.AutomationSecurity Excel.Application.AutomationSecurity = msoAutomationSecurityForceDisable Excel.Workbooks.Open ("C:\Macro.xls") Excel.Application.AutomationSecurity = oldSecurity End Sub Arthur My Blog That is not going to fix his problem. He has a .xlsm file and needs it to be a .xlsx file so that the Excel source can open it.Chuck

You don't need to convert the file at all. You just need to fool SSIS: Quick Reference: How Do I Read an Excel XSLM (Macro-Enabled) File in SSIS? Talk to me now on

Thanks for the reply but the problem is bit different, I daily get one macro enabled excel sheet to load, file name comes is same but different time stamp according to the date. I also have a problem, on Second tab , second column, I have to change the datatype, How can I do it using Script Task. Basically I have to change the formatting type to general for the second column in second tab. So how to achieve this now... Anky

Hi, How can I disable macros in excel before loading the excel sheet in SSIS ? Anky

You can use the excel automation. Place a script task and code the excel automation to disable events in the workbook for all sheets.Please vote as helpful or mark as answer, if it helps Cheers, Raunak | t: @raunakjhawar | My Blog

Can you pls elaborate ... what exactly needs to be done in script task ?Anky